﻿if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SummaryOfSalesByYearReport]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SummaryOfSalesByYearReport]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[sp_SummaryOfSalesByYearReport]

As

Set NoCount On
-- Summary of Sales by Year

select
YearNum,
[QuarterNum],
Count(OrderId) as OrdersCount,
SUM(Subtotal) as SalesAmount

from
(
Select 
YEAR(ShippedDate) as [YearNum], 
'QuarterNum' = CASE 
  when MONTH(ShippedDate) between 1 and 3 then 1
  when MONTH(ShippedDate) between 4 and 6 then 2
  when MONTH(ShippedDate) between 7 and 9 then 3
  when MONTH(ShippedDate) between 10 and 12 then 4
END,
OrderId,
Subtotal 
from
(
	SELECT 
	   [Order].ShippedDate, 
       [Order].ID as OrderID, 
       [OrderSubtotals].Subtotal
	FROM [Order] INNER JOIN
	(
		SELECT 
		  [OrderDetail].OrderID, 
		  Sum(Cast([UnitPrice]*[Quantity]*(1-[Discount])/100  
			as smallmoney)*100) AS Subtotal
		FROM [OrderDetail]
		GROUP BY [OrderDetail].OrderID
	) OrderSubTotals 
	ON [Order].ID = [OrderSubtotals].OrderID
	WHERE ([Order].ShippedDate Is Not Null)
) r
) r1
group by 
YearNum,
[QuarterNum]
order by 
YearNum,
[QuarterNum]
GO
